不要用 in + 子查询

您所在的位置:网站首页 mysql 的锁 不要用 in + 子查询

不要用 in + 子查询

2023-05-26 19:54| 来源: 网络整理| 查看: 265

);

这个 A 表中是有 task_id 这个索引的。

一眼看去这个查询没啥问题,子查询里会利用 task_id 这个索引,然后外面的这个查询会上 id 这个主键索引。

而事实是:

一看 explain ,果然走了全表扫描:

可以看到子查询用上了 taskId 这个索引,而 外部的查询走的是全表扫描,大概扫描了 890W 的数据,所以查询花了 1 分 30 秒。

那么问题来了:明明有主键索引不用,mysql 为什么要选择全表?

那么问题来了:明明有主键索引不用,mysql 为什么要选择全表?

我也不知道。

MySQL 有个优化器,它会决定最终以怎么样的形式、选择那个索引来生成最终的执行计划。

对将要执行的 SQL 而言,优化器会有成本模型,它会根据当前表的一些 估算值结合当前的 SQL 语句进行打分,比如如果用了索引 A 需要多少 I/O 成本、CPU 成本,如果用索引 B 要多少I/O、CPU成本。

总而言之,它有自己的一套规则, 会根据估计值预算成本,根据成本最终生成执行计划。

篇幅有限具体不多介绍,有兴趣的话看下官网: https://dev.mysql.com/doc/refman/8.0/en/cost-model.html

既然是预算,那就有可能不准,所以有时候就会产生该走索引却全表扫描的情况(因为算出的成本走全表扫描反而更低)。

所以针对上面这种情况,我让他加了强制用主键的操作 force index(PRIMARY) ,但是并没有生效,还是走了全表。

可以看到扫描了大约 996w 行数据(表数据有新增,所以从800w多变成了900多w)。

我自己估计:大致就是因为子查询的结果不确定,MySQL 不知道 in 的值到底有多大,所以保守型的选择了全表(我猜的,具体的逻辑估计得看源码,不过最终表现的事实确实如此)。

问题就在 in 的值大小不确定。

通过子查询,我们自己其实也无法保证 in 的值到底是多少。

所以最后的方案是抛弃 in 的方式,采用 inner join 的方式来改造 sql 实现。

改造 SQL 如下:

select* fromA a innerjoin(

selectmax( id) asidfromA

wheretask_id in( 1, 2, 3) groupbytask_id

) b

ona.id = b.id;

这样改造以后,进行了一波 explain,结果如下:

查询结果秒出,可以看下第二行,用上了主键索引,全部扫描的行数加起来也就 600 多。

所以我们换了一种方式来使得 MySQL 正常的用上索引且所要的结果是一致的。

这里也建议,如果 sql 是包含子查询的 in 查询,那么最好换成 join 的方式,因为我们不能保证 in 的查询一定会用上索引,万一来个全表扫描,在表数据量比较大的场景,很容易产生阻塞,多来几个这样的阻塞,数据库连接不可用,服务可能就挂了。

在测试环境看起来没问题,一到生产就 GG。

所以能不用 in + 子查询,就不要用,可以用 join 来替代实现。

程序员专属T恤

商品直购链接👇

据说,这是一件程序员才懂的T恤

记一次 MySQL 主从同步异常的排查记录,百转千回!

mysql的varchar字段最大长度真的是65535吗?

高频面试:如何解决MySQL主从复制延时问题

美团面试特有:写个 SQL 语句然后问加了哪些锁

Office 2019/2021专业增强版,正版终身授权! 返回搜狐,查看更多



【本文地址】


今日新闻


推荐新闻


CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3